alter table RentProjectionUnit
add RenterName varchar(100) null,
	RenterPhone varchar(50) null
go	
update RentProjectionUnit
set RenterName = RentProjection.RenterName, RenterPhone = RentProjection.RenterPhone
from RentProjection
where RentProjectionUnit.Apn = RentProjection.Apn
go
ALTER PROCEDURE [dbo].[getRentProjection]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '',
        
    @UserId INT = NULL
)

AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @f2 DECIMAL(18, 4), @k2 DECIMAL(18, 4), @l2 DECIMAL(18, 4)
	DECLARE @m2 DECIMAL(18, 4), @n2 DECIMAL(18, 4), @p2 DECIMAL(18, 4)
	
	SELECT @f2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentF2'
	
	SELECT @k2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentK2'
	
	SELECT @l2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentL2'
	
	SELECT @m2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentM2'
	
	SELECT @n2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentN2'
	
	SELECT @p2 = ISNULL(CONVERT(DECIMAL(18, 4), REPLACE(Value, ',', '.')), 0) / 100
	FROM Config
	WHERE [Key] = 'RentProjection_PercentP2'
		
	SELECT RentProjection.Apn, RentProjection.PropertyLocation, RentProjection.InvestorId, RentProjection.ZipCode, 
		RentProjection.PurchasePrice, RentProjection.ClosingCost, RentProjection.RehabCost, RentProjection.RentMonth,
		RentProjection.HOAMonth, RentProjection.DateRentedFrom, RentProjection.DateRentedTo, 
		RentProjection.ManagementCompany, RentProjection.Contact, RentProjection.CompanyPhone, RentProjection.CompanyAddress, 
	ua.Fname + ' ' + ua.LName as InvestorName,
	RentProjection.ClosingCost + RentProjection.PurchasePrice AS PurchaseTotal,
	RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost AS TotalAcquisition,
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12) AS TaxesMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @l2) AS AnnualVacancyLossMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @m2) AS MaintenanceMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @n2) AS ManagementMonth,
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth * @p2) AS InsuranceMonth,
	
	CONVERT(DECIMAL(18, 2), RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth) AS MonthlyExpense,
	
	CONVERT(DECIMAL(18, 2), RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) AS MonthlyCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), (RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12) AS AnnualCashFlowProfit,
	
	CONVERT(DECIMAL(18, 2), CASE WHEN (RentProjection.RentMonth = 0) or (RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost = 0) THEN 0
	ELSE 
	(RentProjection.RentMonth - 
	(RentProjection.PurchasePrice * 0.35 * @k2 / 12 + 
	RentProjection.RentMonth * @l2 + 
	RentProjection.RentMonth * @m2 + 
	RentProjection.RentMonth * @n2 + 
	RentProjection.RentMonth * @p2 + 
	RentProjection.HOAMonth)) * 12 /
	(RentProjection.ClosingCost + RentProjection.PurchasePrice + RentProjection.RehabCost) * 100 
	END) AS CashFlowProfitVsTotalAcquisition,
	
	(select max(PropertyId) from LvReo_MasterList where LvReo_MasterList.Apn = RentProjection.Apn) as PropertyId,
	
	RentProjectionUnit.Bathrooms, RentProjectionUnit.Bedrooms,
	RentProjectionUnit.Rehab, RentProjectionUnit.SquareFootage, RentProjectionUnit.UnitNumber,
	RentProjectionUnit.RentProjectionUnitId,
	RentProjectionUnit.RenterName, RentProjectionUnit.RenterPhone
	
	FROM RentProjection LEFT JOIN UserAccounts AS ua ON RentProjection.InvestorId = ua.UserId
			left join RentProjectionUnit on RentProjection.Apn = RentProjectionUnit.Apn
	WHERE (RentProjection.Apn LIKE '%' + @Apn + '%')
		AND (ISNULL(PropertyLocation, '') LIKE '%' + @PropLocation + '%')
		AND (ISNULL(ZipCode, '') LIKE '%' + @Zip + '%') 
		AND (ISNULL(InvestorId, -1) = ISNULL(@UserId, ISNULL(InvestorId, -1)))
END
go
ALTER PROCEDURE [dbo].[updateRentProjection]
(
    @Apn VARCHAR(50),
	@PropertyLocation VARCHAR(400),
	@InvestorId INT,
	@ZipCode VARCHAR(20),
	@PurchasePrice MONEY,
	@ClosingCost MONEY,
	--@RehabCost MONEY,
	@RentMonth MONEY,
	@HOAMonth MONEY,
	
	@DateRentedFrom DATETIME = NULL,
	@DateRentedTo DATETIME = NULL,
	@ManagementCompany VARCHAR(100) = NULL,
	@Contact VARCHAR(100) = NULL,
	@CompanyPhone VARCHAR(50) = NULL,
	@CompanyAddress VARCHAR(400) = NULL,
	
	@RentProjectionUnitId int = null,
	@UnitNumber varchar(50) = null,
	@SquareFootage float = null,
	@Bedrooms int = null,
	@Bathrooms varchar(50) = null,
	@Rehab money = null,
	@RenterName VARCHAR(100) = NULL,
	@RenterPhone VARCHAR(50) = NULL
)

AS
BEGIN
	SET NOCOUNT ON
	set xact_abort on
	
	begin transaction
	
		UPDATE RentProjection
		SET PropertyLocation = @PropertyLocation, InvestorId = @InvestorId, ZipCode = @ZipCode, 
			PurchasePrice = @PurchasePrice, ClosingCost = @ClosingCost, --RehabCost = @RehabCost, 
			RentMonth = @RentMonth, HOAMonth = @HOAMonth,
			DateRentedFrom = @DateRentedFrom, DateRentedTo = @DateRentedTo, 
			ManagementCompany = @ManagementCompany, Contact = @Contact, CompanyPhone = @CompanyPhone, CompanyAddress = @CompanyAddress
		WHERE (Apn = @Apn)
		
		if (@RentProjectionUnitId is null)
			insert into RentProjectionUnit
			(Apn, UnitNumber, SquareFootage, Bedrooms, Bathrooms, Rehab, RenterName, RenterPhone)
			values (@Apn, @UnitNumber, @SquareFootage, @Bedrooms, @Bathrooms, @Rehab, @RenterName, @RenterPhone)
		else
			update RentProjectionUnit
			set UnitNumber = @UnitNumber, SquareFootage = @SquareFootage, Bedrooms = @Bedrooms, Bathrooms = @Bathrooms, Rehab = @Rehab,
				RenterName = @RenterName, RenterPhone = @RenterPhone
			where (RentProjectionUnitId = @RentProjectionUnitId)			
			
	commit transaction
END
go
ALTER PROCEDURE [dbo].[getLvreo_MasterListEx]
(
    @Apn          varchar(50) = '', --Apn Number
    @PropLocation varchar(400) = '', --Property Location
    @Zip          varchar(50) = '', --Zip
    @showAllUnassigned bit = 0
)

AS

BEGIN

declare @percentT1 money
declare @percentAA1 money
set @percentT1 = (select Value from Config where [Key] = 'PercentT1')
set @percentAA1 = (select Value from Config where [Key] = 'PercentAA1')

        SELECT
			ua.Fname + ' ' + ua.LName as InvestorName,
			EMD,
			Reimb,
			Insur,
			Assmts,
			dbo.fnCastAsDate(m.DateAccept) as DateAccept,
			m.Apn,
			m.PropertyLocation,
			m.ZipCode,
			--dbo.fnCastAsDate(StartDate) as StartDate,
			m.RECORDED_F AS StartDate,
			p.Stories,
			p.ConstructYear,
			p.TotalSqft,			
			p.Bedrooms,
			p.Bathrooms,
			p.GarageSqft,
			p.Pool,
			p.Spa,
			cast(dbo.fnCastAsMoneyOrZero(PurchasePrice) as decimal(18,0)) as PurchasePrice
			,ClosingCost = cast(round(dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100, 0) as int)
			,cast(dbo.fnCastAsMoneyOrZero(RehabCosts) as decimal(18,0)) as RehabCosts
			-- PurchasePrice + ClosingCost + RehabCosts
            ,PurchaseNoteTotal =
				cast(round(dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100, 0) as int)		
           -- PurchaseNoteTotal / TotalSqft
           ,PurchaseCostSqft =
				case when dbo.fnCastAsMoneyOrZero(TotalSqft) <> 0 then
					cast(round((dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoneyOrZero(TotalSqft), 1), 2) as decimal(18, 2))
				else 0 end
			,cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)) as EstimatedSale
			-- EstimatedSale / TotalSqft
           ,SaleCostSqft =
				case when dbo.fnCastAsMoneyOrZero(TotalSqft) <> 0 then 
					cast(round(dbo.fnCastAsMoneyOrZero(EstimatedSale) / ISNULL(dbo.fnCastAsMoneyOrZero(TotalSqft), 1), 2) as decimal(18, 2))
				else 0 end
           -- (NetDemandToEscrow - PurchaseNoteTotal)*.15 + EstimatedSale * @percentAA1 / 100
           ,CostOfSale =
				cast(round(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100) - 
				(dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100))*dbo.fnGetCostOfSalePercent(FeePercent)
				+
				dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100, 0) as int)
			,AA = ''
			-- EstimatedSale - EstimatedSale * @percentAA1%
			,NetDemandToEscrow =
				cast(round(dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100, 0) as int)
			-- ([Net Demand to escrow] - PurchaseNoteTotal) * 0.8
			,NetProfit =
				cast(round(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100)
					- (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)) * dbo.fnGetNetProfitPercent(FeePercent), 0) as int)
			-- NetProfit / PurchaseNoteTotal
			,PercentOfProfitVsPurchase =
				case when dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 <> 0 then
					cast(cast(round(100*(((dbo.fnCastAsMoneyOrZero(EstimatedSale) - dbo.fnCastAsMoneyOrZero(EstimatedSale) * @percentAA1 / 100) - (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)) * .8 / (dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100)), 1) as decimal(18, 1)) as varchar) + '%'
				else '0%' end	
			-- PurchaseNoteTotal / EstimatedSale
			,CostToARV =
				case when ISNULL(dbo.fnCastAsMoneyOrZero(EstimatedSale), 1) <> 0 then
					cast(cast(round(100*((dbo.fnCastAsMoneyOrZero(PurchasePrice) + dbo.fnCastAsMoneyOrZero(RehabCosts) + dbo.fnCastAsMoneyOrZero(PurchasePrice) * @percentT1 / 100) / ISNULL(dbo.fnCastAsMoneyOrZero(EstimatedSale), 1)), 1) as decimal(18, 1)) as varchar) + '%'
				else '0%' end
			,dbo.fnCastAsDate(CloseDate) as CloseDate
			,[# Days] = ''
			-- CloseDate - StartDate
			--,TotalDays = datediff(DAY, dbo.fnCastAsDate(StartDate), dbo.fnCastAsDate(CloseDate))
			,TotalDays = datediff(DAY, RECORDED_F, dbo.fnCastAsDate(CloseDate))
			, Recorded_B
        ,m.PropertyId, m.ListingStatus, dbo.fnCastAsDateTime(m.AuctionDate) AS AuctionDate, m.TsNumber,   m.Trustee, m.Site, 
                m.EstimatedBid, m.TsOpeningBid, m.ApnOpeningBid, m.vo, m.Hoa, m.MaxBid, 
               m.EstVal, m.Tax, m.Nos, m.Liens, m.OnOff, m.PropertyDetails, m.DocumentNumber, dbo.fnCastAsDate(m.DateRecorded) as DateRecorded,
               p.LotSize,  p.LandUse, 
                 p.AssessorDesc, m.Phase,
               Client,  EscrowCompany, MiscNotes, PrelistStatus,   Source,
                InvestorId, case when isnull(FeePercent, '') = '' then '20' else FeePercent end as FeePercent
                
			,cast(NetProfit as int) as NetProfit2
			,Bedrooms
			,Bathrooms
			,ConstructYear
			,GarageSqft
			,[Pool]
			,TotalSqft,
			m.DeedRestrictions, m.RehabMiscCosts + m.RehabEstimate AS CurrentRehabCosts, 
			m.NetSheet_SellingPrice AS ActualSale, 
			convert(int, m.NetSheet_SellingPrice - ISNULL(CONVERT(money, m.EstimatedSale), 0)) AS ActualvsEstimate,
			convert(varchar, case when cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)) <> 0 then
				convert(int, (m.NetSheet_SellingPrice - ISNULL(CONVERT(money, m.EstimatedSale), 0)) * 100 / cast(dbo.fnCastAsMoneyOrZero(EstimatedSale) as decimal(18,0)))
			else 0
			end) + '%' as ActualvsEstimatePercent,
			m.[Type]
        FROM (SELECT * 
              FROM vwLvReo_MasterList 
              WHERE Apn LIKE '%'+@Apn+'%'
              AND isnull(PropertyLocation, '') LIKE '%'+@PropLocation+'%'
              AND isnull(ZipCode, '') LIKE '%'+@Zip+'%')  AS m          
         LEFT OUTER JOIN LvReo_ParcellAppraisal AS p ON m.Apn = p.Apn 
         left join UserAccounts ua on m.InvestorId = ua.UserId
        where
			case
				when @showAllUnassigned = 0 then 1
				when (@showAllUnassigned = 1) and (ListingStatus = 'Unassigned') and (isnull(CloseDate, '') = '') then 1
				else 0
			end = 1
        ORDER BY
			case when ISNULL(Recorded_B, '') = '' then m.ZipCode else 'z' end

END
go
ALTER PROCEDURE [dbo].[updateLvreo_MasterListEx]
(
    @PropertyId        int, --Id
    @Apn               varchar(50)='', --Apn Key 
    @PropertyLocation  varchar(400)='', --Prop Location  
    @ZipCode           varchar(50)='', --ZipCode 
    @Stories           varchar(50)='', --Stories 
    @ConstructYear     varchar(50)='', --Const Year 
    @TotalSQft         varchar(50)='', --Total SQft 
    @Bedrooms          varchar(50)='', -- Bed 
    @Bathrooms         varchar(50)='', -- Bath 
    @GarageSqft        varchar(50)='', --GarageSqft 
    @Pool              varchar(50)='', --Pool 
    @Spa               varchar(50)='', --Spa 
	@Recorded_B        varchar(50)='',
	@PurchasePrice     varchar(50)='',
	@RehabCosts        varchar(50)=''
	,@investorName		varchar(50)=''
	,@EMD varchar(50)=''
	,@Reimb varchar(50)=''
	,@Insur varchar(50)=''
	,@DateAccept varchar(50)=''
	,@StartDate varchar(50)=''
	,@EstimatedSale varchar(50)=''
	,@Assmts varchar(50)=''
	,@FeePercent varchar(50)=''
	,@netProfit2 int
	,@DeedRestrictions varchar(200)=''
	,@Type varchar(100)
)

AS

BEGIN
        UPDATE LvReo_ParcellAppraisal
        SET Stories = @Stories,
            ConstructYear = @ConstructYear,
            TotalSqft = @TotalSQft,
            Bedrooms = @Bedrooms,
            Bathrooms = @Bathrooms,
            GarageSqft = @GarageSqft,
            Pool = @Pool,
            Spa = @Spa,
            Apn = @Apn
        WHERE Apn = @Apn
        
        if @@ROWCOUNT = 0
			insert into LvReo_ParcellAppraisal(Stories, ConstructYear, TotalSqft, Bedrooms, Bathrooms,
				GarageSqft, Pool, Spa, Apn)
			values(@Stories, @ConstructYear, @TotalSqft, @Bedrooms, @Bathrooms,
				@GarageSqft, @Pool, @Spa, @Apn)

        UPDATE LvReo_MasterList
        SET 
            PropertyLocation = @PropertyLocation,
            ZipCode = @ZipCode,
			Recorded_B = @Recorded_B,
			PurchasePrice = @PurchasePrice,
			RehabCosts = @RehabCosts,
			Apn = @Apn,
			InvestorId = @investorName
			,EMD = @EMD
			,Reimb = @Reimb
			,Insur = @Insur
			,DateAccept = @DateAccept
			,RECORDED_F = @StartDate
			,EstimatedSale = @EstimatedSale
			,Assmts = @Assmts
			,FeePercent = case when @FeePercent = '20' then null else @FeePercent end
			,NetProfit = @netProfit2
			,DeedRestrictions = @DeedRestrictions
			,[Type] = @Type
        WHERE PropertyId = @PropertyId
END
go